const express = require('express');
// 加载MySQL模块
const mysql = require('mysql');
// 加载bodyParser模块
const bodyParser = require('body-parser');
// 加载MD5模块
const md5 = require('md5');
// 创建MySQL连接池
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  // password: '',        //数据库用户密码
  password: '9OsTle$hYYObi1jF$CkA@wvO3X%vuFS0e',        //线上
  database: 'beiwo',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});
// 创建服务器对象
const server = express();
server.use(bodyParser.urlencoded({
  extended: false
}));


// 加载CORS模块
const cors = require('cors');
// 使用CORS中间件
server.use(cors({
  origin: ['http://localhost:8080', 'http://127.0.0.1:8080', 'http://127.0.0.1:8081','http://localhost:8081']
}));

// 获取所有成功案例分类的接口
server.get('/cases', (req, res) => {
  // SQL语句以获取文章分类表的数据
  let sql = 'select * from cases';
  // 执行SQL语句
  pool.query(sql, (error, results) => {
    if (error) throw error;
    res.send({ message: 'ok', code: 200, results: results });
  });
});

server.listen(3000, () => {
  console.log('server is running...');
});


//用户注册
server.post('/register', (req, res) => {
  // 获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = 'SELECT COUNT(id) AS count FROM beiwo_user WHERE username=?';
  pool.query(sql, [username], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT beiwo_user(id,username,password) VALUES(null,?,MD5(?))';
      pool.query(sql, [username, password], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 ,result:results[0]});
      })
    } else {
      res.send({ message: 'user exists', code: 201 });
    }
  });
});



// 用户登录接口
server.post('/login', (req, res) => {
  //获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  // SQL语句
  let sql = 'SELECT id,username,nickname,avatar FROM beiwo_user WHERE username=? AND password=MD5(?)';
  pool.query(sql, [username, password], (error, results) => {
    if (error) throw error;
    if(results.length == 0){ //登录失败
      res.send({message:'login failed',code:201});
    } else {                 //登录成功
      res.send({message:'ok',code:200,result:results[0]});
    }
  });
});



//装修案例分页查询
server.get('/articles', (req, res) => {
  // 获取客户端传递的cid参数
  let category_id = req.query.category_id;
  // 获取客户端传递的page参数
  let page = req.query.page? req.query.page : 1;
  // 存储每页显示的记录数
  let pagesize = 6;
  // 通过公式来计算从第几条记录开始返回
  let offset = (page - 1) * pagesize;
  // 用于存储获取到的总记录数
  let rowcount;
  // 获取指定分类下的文章总数
  let sql = 'SELECT COUNT(category_id) AS count FROM cases WHERE category_id=?';
  pool.query(sql, [category_id], (error, results) => {
    if (error) throw error;
    // 将获取到总记录数赋给rowcount变量
    rowcount = results[0].count;
    // 根据总记录数和每页显示的记录数来计算总页数
    let pagecount = Math.ceil(rowcount / pagesize);
    // 查询SQL语句
    sql = 'SELECT * FROM cases WHERE category_id=? LIMIT ?,?';
    // 执行SQL
    pool.query(sql, [category_id, offset, pagesize], (error, results) => {
      if (error) throw error;
      res.send({ message: 'ok', code: 200, results: results, pagecount: pagecount });
    });
  });
});



//订单
server.post('/order',(req,res)=>{
  let user=req.body.user;
  let orderId=req.body.order;
  let title=req.body.title;
  let area=req.body.area;
  let style=req.body.style;
  let Decoration_dismantle=req.body.Decoration_dismantle;
  let Decoration_water=req.body.Decoration_water;
  let Decoration_tax=req.body.Decoration_tax;
  let Decoration_basis=req.body.Decoration_basis;
  let Decoration_sum=req.body.Decoration_sum;
  let dataFurniture_custom=req.body.dataFurniture_custom;
  let dataFurniture_other=req.body.dataFurniture_other;
  let dataFurniture_device=req.body.dataFurniture_device;
  let dataFurniture_adornment=req.body.dataFurniture_adornment;
  let dataFurniture_sum=req.body.dataFurniture_sum;
  let sum=req.body.sum;
  let value=req.body.value;
  let imageUrl=req.body.imageUrl;
  let sql='insert into order_form values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
  pool.query(sql,[user,orderId,title,area,style,Decoration_dismantle,Decoration_water,Decoration_tax,Decoration_basis,Decoration_sum,dataFurniture_custom,dataFurniture_other,dataFurniture_device, dataFurniture_adornment,dataFurniture_sum,sum,value,0,0,imageUrl],(err,results)=>{
    if(err) throw err;
    if(results.length==0){
      res.send({message:'签约失败',code:201});
    }else {
      res.send({message:'ok',code:200,result:results})
    }
  })
});



//个人订单查询未签约
server.post('/userOrder',(req,res)=>{
  let user=req.body.user;
  let sql='select*from order_form where user=? and sign=0 and payment=0';
  pool.query(sql,[user],(err,results)=>{
    if(err) throw err;
      res.send({message:'ok',code:200,result:results});
  })
});


//个人订单查询未付款
server.post('/userPay',(req,res)=>{
  let user=req.body.user;
  let sql='select*from order_form where user=? and sign=1 and payment=0';
  pool.query(sql,[user],(err,results)=>{
    if(err) throw err;
      res.send({message:'ok',code:200,result:results});

  })
});


//已完成订单
server.post('/carry',(req,res)=>{
  let user=req.body.user;
  let sql='select*from order_form where user=? and sign=1 and payment=1';
  pool.query(sql,[user],(err,results)=>{
    if(err) throw err;
      res.send({message:'ok',code:200,result:results});
  })
})

//订单详情查询
server.post('/list',(req,res)=>{
  let user=req.body.user;
  let id=req.body.id;
  let sql='select*from order_form where user=? and id=?';
  pool.query(sql,[user,id],(err,results)=>{
    if(err) throw err;
    if(results.length==0){
      res.send({message:'查询失败',code:201});
    }else {
      res.send({message:'ok',code:200,result:results});
    }
  })
})




//签约
server.post('/sign',(req,res)=>{
     let user=req.body.user;
     let id=req.body.id;//订单id
     let sql='update order_form set sign=1 where user=? and id=?';
     pool.query(sql,[user,id],(err,results)=>{
       if(err) throw err;
       if(results.length==0){
         res.send({message:'付款失败',code:201});
       }else {
         res.send({message:'ok',code:200,result:results});
       }
     })
});



//付款
server.post('/pay',(req,res)=>{
  let user=req.body.user;
  let id=req.body.id;//订单id
  let sql='update order_form set payment=1 where user=? and id=?';
  pool.query(sql,[user,id],(err,results)=>{
    if(err) throw err;
      res.send({message:'ok',code:200,result:results});
  })
})






//案例查询
server.post('/cases',(req,res)=>{
  let projectOrderId=req.body.projectOrderId;
  let sql='select*from cases where projectOrderId=?';
  pool.query(sql,[projectOrderId],(err,results)=>{
    if(err) throw err;
    if(results.length==0){
      res.send({message:'失败',code:201});
    }else {
      res.send({message:'ok',code:200,result:results[0]})
    }
  })
})


//商品评论发布
server.post('/release',(req,res)=>{
  let orderId=req.body.orderId;
  let user=req.body.user;
  let name=req.body.name;
  let score=req.body.score;
  let content=req.body.content;
  let style=req.body.style;
  let area=req.body.area;
  let value=req.body.value;
  let sql='insert into comment (id,orderId,user,name,score,content,style,area,value) values(null,?,?,?,?,?,?,?,?)';
  pool.query(sql,[orderId,user,name,score,content,style,area,value],(err,result)=>{
    if(err) throw err;
    if(result.affectedRows>0){
      res.send({message:'ok',code:200});
    }else{
      res.send({message:'失败',code:201});
    }
  })
})


//商品评论查询
server.get('/comment',(req,res)=>{
  let orderId=req.query.orderId;
  let sql='select*from comment where orderId=?';
  pool.query(sql,[orderId],(err,results)=>{
    if(err) throw err;
      res.send({message:'ok',code:200,result:results})
  })
})








